import sqlite3
import logging
from typing import Optional, List, Tuple, Any

logger = logging.getLogger(__name__)

class DatabaseManager:
    """安全的数据库管理类，使用参数化查询防止SQL注入"""
    
    def __init__(self, db_path: str):
        self.db_path = db_path
        self.conn = None
        
    def __enter__(self):
        self.connect()
        return self
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()
        
    def connect(self):
        """连接到数据库"""
        try:
            self.conn = sqlite3.connect(
                self.db_path, 
                check_same_thread=False,
                isolation_level=None
            )
            self.conn.execute("PRAGMA foreign_keys = ON")
            logger.info(f"成功连接到数据库: {self.db_path}")
        except sqlite3.Error as e:
            logger.error(f"数据库连接失败: {e}")
            raise
            
    def close(self):
        """关闭数据库连接"""
        if self.conn:
            self.conn.close()
            self.conn = None
            logger.info("数据库连接已关闭")
            
    def execute(self, query: str, params: tuple = ()) -> Optional[sqlite3.Cursor]:
        """执行SQL查询"""
        try:
            cursor = self.conn.cursor()
            cursor.execute(query, params)
            self.conn.commit()
            return cursor
        except sqlite3.Error as e:
            logger.error(f"SQL执行错误: {e}, 查询: {query}, 参数: {params}")
            self.conn.rollback()
            raise
            
    def fetch_one(self, query: str, params: tuple = ()) -> Optional[Tuple]:
        """获取单条记录"""
        cursor = self.execute(query, params)
        return cursor.fetchone() if cursor else None
        
    def fetch_all(self, query: str, params: tuple = ()) -> List[Tuple]:
        """获取所有记录"""
        cursor = self.execute(query, params)
        return cursor.fetchall() if cursor else []
        
    def initialize_tables(self):
        """初始化数据库表结构"""
        queries = [
            '''
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT UNIQUE NOT NULL,
                password_hash TEXT NOT NULL,
                salt TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            ''',
            '''
            CREATE TABLE IF NOT EXISTS categories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            ''',
            '''
            CREATE TABLE IF NOT EXISTS tags (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            ''',
            '''
            CREATE TABLE IF NOT EXISTS notes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                content TEXT NOT NULL,
                category_id INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE SET NULL
            )
            ''',
            '''
            CREATE TABLE IF NOT EXISTS note_tags (
                note_id INTEGER,
                tag_id INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (note_id, tag_id),
                FOREIGN KEY (note_id) REFERENCES notes (id) ON DELETE CASCADE,
                FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
            )
            ''',
            # 创建索引以提高查询性能
            '''
            CREATE INDEX IF NOT EXISTS idx_notes_title ON notes (title)
            ''',
            '''
            CREATE INDEX IF NOT EXISTS idx_notes_updated ON notes (updated_at DESC)
            ''',
            '''
            CREATE INDEX IF NOT EXISTS idx_note_tags_note_id ON note_tags (note_id)
            ''',
            '''
            CREATE INDEX IF NOT EXISTS idx_note_tags_tag_id ON note_tags (tag_id)
            '''
        ]
        
        try:
            for query in queries:
                self.execute(query)
            logger.info("数据库表初始化成功")
        except sqlite3.Error as e:
            logger.error(f"数据库表初始化失败: {e}")
            raise
            
    def insert_default_category(self):
        """插入默认分类"""
        try:
            self.execute(
                "INSERT OR IGNORE INTO categories (name) VALUES (?)", 
                ("未分类",)
            )
            logger.info("默认分类已插入")
        except sqlite3.Error as e:
            logger.error(f"插入默认分类失败: {e}")